package cn.edu.zut.cs.impl;

import cn.edu.zut.cs.Dao.StudentDao;
import cn.edu.zut.cs.Model.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Component
public class StudentDaoImpl implements StudentDao {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    static Connection conn;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wedy", "root", "weidi123");
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void addStudent(Student student) {
        String na = student.getName();
        String sql = "SELECT * FROM studen WHERE name = ?";
        // 使用占位符避免 SQL 注入攻击
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) { // conn 为连接数据库的对象
            pstmt.setString(1, na);
            ResultSet rs = pstmt.executeQuery(); // 执行查询操作
            if (!rs.next()) {
                String sqll = "insert into studen (rollNo, name) values(" + student.getRollNo() + " ,'" + student.getName() + "')";
                PreparedStatement ps = conn.prepareStatement(sqll);
                ps.executeUpdate(sqll);
                System.out.println("Student: name " + student.getName()
                        + ", insert into database");
            } else {
                // 数据存在的处理逻辑
                System.out.println("该生在表中已存在");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
//        String sql = "insert into student (rollNo, name) values(?,?)";
//        Object[] args = {student.getRollNo(), student.getName()};
//        System.out.println(sql);
//        System.out.println(args);
//        System.out.println(student.getRollNo());
//        System.out.println(student.getName());
//        jdbcTemplate.update(sql,args);

    }

    @Override
    public void deleteStudent(Student student) {
        String sql = "SELECT * FROM studen WHERE name = ?";
        // 使用占位符避免 SQL 注入攻击
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) { // conn 为连接数据库的对象
            pstmt.setString(1, student.getName());
            ResultSet rs = pstmt.executeQuery(); // 执行查询操作
            if (rs.next()) {
                pstmt.setString(1, student.getName());
                pstmt.executeUpdate();
                System.out.println("Student: Roll No " + student.getRollNo()
                        + ", deleted from database");
            } else {
                // 数据存在的处理逻辑
                System.out.println("该生在表中不存在");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void deleteeStudent(Student student) {
        PreparedStatement statement = null;
        try {
            ResultSet rss = statement.executeQuery("SELECT * FROM studen WHERE name =='student.getName()'");
            if (rss == null) {
                System.out.println("数据不存在");
                return;
            } else {
                System.out.println("数据存在");
                String sql = "DELETE FROM studen WHERE name = ?";
                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                    pstmt.setString(1, student.getName());
                    pstmt.executeUpdate();
                    System.out.println("Student: Roll No " + student.getRollNo()
                            + ", deleted from database");
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Student getStudent(String name) {
        String sql = "SELECT * FROM studen WHERE name = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                int rollNO = rs.getInt("rollNo");
                Student student = new Student(name, rollNO);
                return student;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public void updateStudent(Student student) {
        String sql = "UPDATE studen SET rollNo = ? WHERE name = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, student.getRollNo());
            pstmt.setString(2, student.getName());
            pstmt.executeUpdate();
            System.out.println("Student: name " + student.getName()
                    + ", updated in the database");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM studen";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int rollNo = rs.getInt("rollNo");
                String name = rs.getString("name");
                Student student = new Student(name, rollNo);
                students.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return students;
    }

    @Override
    public Student getStudent(Student student) {
        return null;
    }

    @Override
    public Student getStudentByNo(int rollNo) {
        return null;
    }
}